﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.IO;
using System.Collections;
using System.Reflection;

namespace CSharp.Libs.Misc
{
    public class DataLogSQLite : IDisposable
    {
        #region declarations
        private SQLiteConnection _CON = null;

        private string _strDatabaseFileName = "";
        private string _strCreateDbSql = "";
        private bool _fDbOpen = false;
        #endregion



        #region constructor / finalizer
        public DataLogSQLite()
        {
            //set standard sql string for database creation
            _setStandardDatabaseSQLString();
        }

        #region Disposable Pattern (http://msdn.microsoft.com/en-us/library/fs2xkftw.aspx)
        private bool _fDisposed = false;
        public void Dispose()
        {
            Dispose(true);

            // Use SupressFinalize in case a subclass 
            // of this type implements a finalizer.
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            // If you need thread safety, use a lock around these  
            // operations, as well as in your methods that use the resource. 
            if (!_fDisposed)
            {
                if (disposing)
                {
                    if (_CON != null) { _CON.Close(); _CON.Dispose(); }
                    //Console.WriteLine("Object disposed.");
                }

                // Indicate that the instance has been disposed.
                _CON = null;
                _fDisposed = true;
            }
        }
        #endregion
        #endregion



        #region private functions
        #region *   database creation
        /// <summary>sets the old default database creation sql string</summary>
        /// <history>created  17.12.2012</history>
        private void _setStandardDatabaseSQLString()
        {
            try
            {
                _strCreateDbSql = String.Concat(
                    "CREATE TABLE [tblAttachments] (",
                    "[ID] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,",
                    "[DataLogItemId] INTEGER  NULL,",
                    "[Type] TEXT  NULL,",
                    "[FileName] TEXT  NULL,",
                    "[Data] BLOB  NULL",
                    ");",
                    Environment.NewLine,
                    "CREATE TABLE [tblDataLogItems] (",
                    "[ID] INTEGER  PRIMARY KEY AUTOINCREMENT NOT NULL,",
                    "[LogTime] TEXT  NULL,",
                    "[Level] TEXT  NULL,",
                    "[Category] TEXT  NULL,",
                    "[Tags] TEXT  NULL,",
                    "[Message] TEXT  NULL",
                    ");",
                    Environment.NewLine,
                    "CREATE INDEX [idxDataLogItems] ON [tblDataLogItems](",
                    "[Level]  ASC,",
                    "[Tags]  ASC,",
                    "[Message]  ASC",
                    ");");
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_InitDB</summary>
        /// <history>17.12.2012</history>
        private void _InitDB(
            String DatabaseFileName)
        {
            try
            {
                _strDatabaseFileName = DatabaseFileName;

                if (!File.Exists(_strDatabaseFileName))
                {
                    _CreateDB();
                }

                if (File.Exists(_strDatabaseFileName))
                {
                    _OpenDB();
                }
                else
                {
                    FileNotFoundException _exFileNotFound = new FileNotFoundException(
                        "Specified File couldn't be created.",
                        _strDatabaseFileName);
                }
            }
            catch (Exception ex)
            {
            }
        }

        /// <summary>_CreateDB</summary>
        /// <history>24.12.2012</history>
        private void _CreateDB()
        {
            try
            {
                //Make sure DB does not exist
                if (!File.Exists(_strDatabaseFileName))
                {
                    using (SQLiteConnection CON = new SQLiteConnection(String.Concat("Data Source=", _strDatabaseFileName)))
                    {
                        CON.Open();

                        using (SQLiteCommand CMD = CON.CreateCommand())
                        {
                            CMD.CommandText = _strCreateDbSql;

                            try
                            {
                                CMD.ExecuteNonQuery();
                            }
                            catch (Exception ex)
                            {
                                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                            }
                        }

                        CON.Close();
                    }
                }
                else
                {
                    //_KRNL.EC.AddException(new FileLoadException("ERROR in DatabaseOperations._CreateDB: File already exists so I better do nothing. Otherwise an existing database could get overwritten completely.", _strDatabaseFileName), this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                }
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }

        /// <summary>_OpenDatabase</summary>
        /// <history>24.12.2012</history>
        private void _OpenDB()
        {
            try
            {
                if (File.Exists(_strDatabaseFileName))
                {
                    //Setting Cache to 600MB for performance reasons
                    //Testphase
                    _CON = new SQLiteConnection(String.Concat(
                        "Data Source=", _strDatabaseFileName, ";",
                        "Cache Size=600000;",
                        "Page Size=1024"));
                    _CON.Open();
                    _fDbOpen = true;
                }
                else
                {
                    _fDbOpen = false;
                    FileNotFoundException _exFileNotFound = new FileNotFoundException(
                        "ERROR opening SQLiteDB: File not found.",
                        _strDatabaseFileName);
                    //_KRNL.EC.AddException(_exFileNotFound, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
                }
            }
            catch (Exception ex)
            {
                _fDbOpen = false;
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }
        }
        #endregion



        #region *   save data
        /// <summary>saves a DataLogItem</summary>
        /// <param name="dli">DataLogItem to be saved</param>
        /// <returns>the ID of the successfully saved DataLogItem. If save failed, returns 0.</returns>
        /// <history>created  25.12.2012</history>
        private Int64 _saveDataLogItem(
            DataLogItem dli)
        {
            try
            {
                //don't save DataLogItem if not valid
                if (!_dataLogItemIsValid(dli)) { return 0; }

                //column and param names
                const string LOG_TIME = "LogTime";
                const string LEVEL = "Level";
                const string CATEGORY = "Category";
                const string TAGS = "Tags";
                const string MESSAGE = "Message";
                string prmLogTime = string.Concat("@", LOG_TIME, "Param");
                string prmLevel = string.Concat("@", LEVEL, "Param");
                string prmCategory = string.Concat("@", CATEGORY, "Param");
                string prmTags = string.Concat("@", TAGS, "Param");
                string prmMessage = string.Concat("@", MESSAGE, "Param");

                //Create command SQL strings
                String strCmdDataLogItemsTable = String.Concat(
                    "INSERT INTO tblDataLogItems(", LOG_TIME, ", ", LEVEL, ", ", CATEGORY, ", ", TAGS, ", ", MESSAGE, ") ",
                    "VALUES (", prmLogTime, ", ", prmLevel, ", ", prmCategory, ", ", prmTags, ", ", prmMessage, ");",
                    Environment.NewLine,
                    "SELECT last_insert_rowid() AS RecordID;");

                //build command object
                SQLiteCommand slcDataLogItem = new SQLiteCommand(strCmdDataLogItemsTable, _CON);
                slcDataLogItem.Parameters.AddWithValue(prmLogTime, dli.LogTime);
                slcDataLogItem.Parameters.AddWithValue(prmLevel, dli.Level);
                slcDataLogItem.Parameters.AddWithValue(prmCategory, dli.Category);
                slcDataLogItem.Parameters.AddWithValue(prmTags, dli.Tags);
                slcDataLogItem.Parameters.AddWithValue(prmMessage, dli.Message);

                //submit command and receive newly created InformationID
                Int64 intID = Convert.ToInt64(slcDataLogItem.ExecuteScalar());

                return intID;
            }
            catch (Exception ex)
            {
                return 0; //return 0 as unsuccessful behavior
                throw ex;
            }
        }

        /// <summary>checks if the DataLogItem is valid i.e. the logtime has a valid format</summary>
        /// <param name="dli">DataLogItem to validate</param>
        /// <returns>true if DataLogItem is valid, false if </returns>
        /// <history>created  25.12.2012</history>
        private bool _dataLogItemIsValid(
            DataLogItem dli)
        {
            try
            {
                //1. LogTime has to be a valid DateTime format
                DateTime dtLogTime;
                if (!DateTime.TryParse(dli.LogTime, out dtLogTime)) { return false; }

                //every test succeeded
                return true;
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
        }
        #endregion



        #region *   read data
        #region *   *   get levels and categories
        /// <summary>gets a list of available levels</summary>
        /// <returns></returns>
        /// <history>created  30.12.2012</history>
        private ArrayList _getLevels()
        {
            try
            {
                //prepare return value
                ArrayList aryLevels = new ArrayList();

                //prepare SQL string
                string strSQL = string.Concat(
                    "SELECT Level FROM tblDataLogItems ",
                    "GROUP BY Level ",
                    "ORDER BY Level ASC");

                //prepare command
                SQLiteCommand cmdGetLevels = new SQLiteCommand(strSQL, _CON);

                //execute it
                using (SQLiteDataReader drGetLevels = cmdGetLevels.ExecuteReader())
                {
                    //quit if no rows exist
                    if (!drGetLevels.HasRows) { return null; }

                    //read rows
                    while (drGetLevels.Read())
                    {
                        string strLevel = drGetLevels["Level"].ToString();
                        aryLevels.Add(strLevel);
                    }
                }

                //return result
                if (aryLevels.Count > 0) { return aryLevels; }

                //or null if no levels were defined
                return null;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
        }

        /// <summary>gets a list of available categories</summary>
        /// <returns></returns>
        /// <history>created  30.12.2012</history>
        private ArrayList _getCategories()
        {
            try
            {
                //prepare return value
                ArrayList aryCategories = new ArrayList();

                //prepare SQL string
                string strSQL = string.Concat(
                    "SELECT Category FROM tblDataLogItems ",
                    "GROUP BY Category ",
                    "ORDER BY Category ASC");

                //prepare command
                SQLiteCommand cmdGetCategoies = new SQLiteCommand(strSQL, _CON);

                //execute it
                using (SQLiteDataReader drGetCategories = cmdGetCategoies.ExecuteReader())
                {
                    //quit if no rows exist
                    if (!drGetCategories.HasRows) { return null; }

                    //read rows
                    while (drGetCategories.Read())
                    {
                        string strCategory = drGetCategories["Category"].ToString();
                        aryCategories.Add(strCategory);
                    }
                }

                //return result
                if (aryCategories.Count > 0) { return aryCategories; }

                //or null if no levels were defined
                return null;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
        }
        #endregion

        #region *   *   query
        /// <summary>queries the DataLogItems database</summary>
        /// <param name="strFromTime">return only entries which time is after the given time</param>
        /// <param name="strToTime">return only entries which time is before the given time</param>
        /// <param name="strLevel">only return entries which level is the same as the given one</param>
        /// <param name="strCategory">only return entries which category is the same as the given one</param>
        /// <param name="strTags">only return entries which Tags are the same as the given one</param>
        /// <param name="strMessageFilter">only return entries that contain the given string</param>
        /// <remarks>if all parameters are left blank, the query returns all DataLogItems contained in the database</remarks>
        /// <returns>ArrayList filled with DataLogItems representing the resultset of the query</returns>
        /// <history>created  01.01.2013</history>
        private ArrayList _queryLogItems(
            string strFromTime,
            string strToTime,
            string strLevel,
            string strCategory,
            string strTags,
            string strMessageFilter)
        {
            try
            {
                //prepare return value
                ArrayList aryLogItemsQueryResult = new ArrayList();

                //prepare SQL string
                string strSQL = string.Concat(
                    "SELECT * FROM tblDataLogItems ",
                    _getWhereClause(strFromTime, strToTime, strLevel, strCategory, strTags, strMessageFilter),
                    "ORDER BY LogTime ASC");

                //prepare command
                SQLiteCommand cmdQueryDataLogItems = new SQLiteCommand(strSQL, _CON);

                //execute it
                using (SQLiteDataReader drQueryDataLogItems = cmdQueryDataLogItems.ExecuteReader())
                {
                    //quit if no rows exist
                    if (!drQueryDataLogItems.HasRows) { return null; }

                    //read rows
                    while (drQueryDataLogItems.Read())
                    {
                        DataLogItem dli = new DataLogItem
                        {
                            LogTime=drQueryDataLogItems["LogTime"].ToString(),
                            Level=drQueryDataLogItems["Level"].ToString(),
                            Category = drQueryDataLogItems["Category"].ToString(),
                            Tags = drQueryDataLogItems["Tags"].ToString(),
                            Message = drQueryDataLogItems["Message"].ToString()
                        };

                        aryLogItemsQueryResult.Add(dli);
                    }
                }

                //return result
                if (aryLogItemsQueryResult.Count > 0) { return aryLogItemsQueryResult; }

                //or null if no levels were defined
                return null;
            }
            catch (Exception ex)
            {
                return null;
                throw ex;
            }
        }

        /// <summary>puts together a sql query where clause by the given parameters</summary>
        /// <returns>A where clause which suites the given parameters. 
        /// The parameters are AND concatenated. 
        /// If only empty parameters are given, the function returns an ampty string which results in an empty return string.</returns>
        /// <history>created  01.01.2013</history>
        private string _getWhereClause(
            string strFromTime,
            string strToTime,
            string strLevel,
            string strCategory,
            string strTags,
            string strMessageFilter)
        {
            try
            {
                //if there are only empty strings, return nothing
                if (strFromTime.Trim() == "" &&
                    strToTime.Trim() == "" &&
                    strLevel.Trim() == "" &&
                    strCategory.Trim() == "" &&
                    strTags.Trim() == "" &&
                    strMessageFilter.Trim() == "")
                { return ""; }

                //prepare result string
                string strWhereClause = "";

                //create time filter
                if (strFromTime.Trim() != "") { strWhereClause = " LogTime >= '" + strFromTime.Trim() + "' "; }
                if (strToTime.Trim() != "")
                {
                    if (strWhereClause.Trim() != "") { strWhereClause += " AND "; }
                    strWhereClause += " LogTime <= '" + strToTime.Trim() + "' ";
                }

                //create level filter
                if (strLevel.Trim() != "")
                {
                    if (strWhereClause.Trim() != "") { strWhereClause += " AND "; }
                    strWhereClause += " Level = '" + strLevel.Trim() + "' ";
                }

                //create category filter
                if (strCategory.Trim() != "")
                {
                    if (strWhereClause.Trim() != "") { strWhereClause += " AND "; }
                    strWhereClause += " Category = '" + strCategory.Trim() + "' ";
                }

                //create tags filter
                if (strTags.Trim() != "")
                {
                    if (strWhereClause.Trim() != "") { strWhereClause += " AND "; }
                    strWhereClause += " Tags = '" + strTags.Trim() + "' ";
                }

                //create message filter
                if (strMessageFilter.Trim() != "")
                {
                    if (strWhereClause.Trim() != "") { strWhereClause += " AND "; }
                    strWhereClause += " Message Like '%" + strMessageFilter.Trim() + "%' ";
                }

                return "WHERE " + strWhereClause + " ";
            }
            catch (Exception ex)
            {
                return "";
                throw ex;
            }
        }
        #endregion
        #endregion



        #region *   basic functions
        /// <summary>_GetBoolean</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>created  15.04.2011</history>
        /// <history>modified 25.11.2011: Added evaluating bool as string</history>
        private Boolean _GetBoolean(
            Object Input)
        {
            try
            {
                Int64 RetVal = -1;
                if (Int64.TryParse(Input.ToString(), out RetVal))
                {
                    if (RetVal != 0)
                    {
                        return true;
                    }
                }
                else
                {
                    if (Input.ToString().ToLower() == "true")
                    {
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return false;
        }

        /// <summary>_GetInt64</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Int64 _GetInt64(
            Object Input)
        {
            try
            {
                Int64 RetVal = -1;
                Int64.TryParse(Input.ToString(), out RetVal);
                return RetVal;
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return -1;
        }

        /// <summary>_GetDouble</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.05.2011</history>
        private Double _GetDouble(
            Object Input)
        {
            try
            {
                Double RetVal = -1;
                Double.TryParse(Input.ToString(), out RetVal);
                return RetVal;
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return -1;
        }

        /// <summary>_SetBooleanString</summary>
        /// <param name="Input"></param>
        /// <param name="asString"></param>
        /// <returns></returns>
        /// <history>created  25.11.2011</history>
        private String _SetBooleanString(
            Boolean Input)
        {
            try
            {
                if (Input)
                {
                    return "True";
                }
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return "False";
        }

        /// <summary>_SetBoolean</summary>
        /// <param name="Input"></param>
        /// <returns></returns>
        /// <history>15.04.2011</history>
        private Int64 _SetBoolean(
            Boolean Input)
        {
            try
            {
                if (Input)
                {
                    return 1;
                }
            }
            catch (Exception ex)
            {
                //_KRNL.EC.AddException(ex, this.ToString(), _KRNL.EC.Tools.GetCurrentMethodName());
            }

            return 0;
        }

        /// <summary>_TableExists</summary>
        /// <param name="CON"></param>
        /// <param name="strTableName"></param>
        /// <returns></returns>
        /// <history>02.08.2011</history>
        private Boolean _TableExists(
            SQLiteConnection CON,
            String strTableName)
        {
            SQLiteCommand CMD = new SQLiteCommand(CON);
            CMD.CommandText = String.Concat("SELECT name FROM sqlite_master WHERE name='", strTableName, "'");

            SQLiteDataReader DR = CMD.ExecuteReader();

            if (DR.HasRows)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #endregion



        #region interface
        #region properties
        public String DatabaseFileName { get { return _strDatabaseFileName; } }

        public string CreateDbSql { get { return _strCreateDbSql; } set { _strCreateDbSql = value; } }

        public Boolean DbOpen { get { return _fDbOpen; } }
        #endregion

        #region methods
        public void InitDb()
        {
            _InitDB(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\DataLog.s3db");
        }

        public void InitDb(
            String DatabaseFileName)
        {
            _InitDB(DatabaseFileName);
        }

        public void InitDb(
            String DatabaseFileName,
            string CreateSqlDbString)
        {
            _strCreateDbSql = CreateSqlDbString;

            _InitDB(DatabaseFileName);
        }

        /// <summary>checks if the DataLogItem is valid i.e. the logtime has a valid format</summary>
        /// <param name="dli">DataLogItem to validate</param>
        /// <returns>true if DataLogItem is valid, false if </returns>
        /// <history>created  25.12.2012</history>
        public bool DataLogItemIsValid(
            DataLogItem dli)
        {
            return _dataLogItemIsValid(dli);
        }

        /// <summary>saves a DataLogItem</summary>
        /// <param name="dli">DataLogItem to be saved</param>
        /// <returns>the ID of the successfully saved DataLogItem. If save failed, returns 0.</returns>
        /// <history>created  25.12.2012</history>
        public Int64 SaveDataLogItem(
            DataLogItem dli)
        {
            return _saveDataLogItem(dli);
        }

        /// <summary>gets a list of available levels</summary>
        /// <returns></returns>
        /// <history>created  30.12.2012</history>
        public ArrayList GetLevels()
        {
            return _getLevels();
        }

        /// <summary>gets a list of available categories</summary>
        /// <returns></returns>
        /// <history>created  30.12.2012</history>
        public ArrayList GetCategories()
        {
            return _getCategories();
        }

        /// <summary>queries the DataLogItems database</summary>
        /// <param name="strFromTime">return only entries which time is after the given time</param>
        /// <param name="strToTime">return only entries which time is before the given time</param>
        /// <param name="strLevel">only return entries which level is the same as the given one</param>
        /// <param name="strCategory">only return entries which category is the same as the given one</param>
        /// <param name="strTags">only return entries which Tags are the same as the given one</param>
        /// <param name="strMessageFilter">only return entries that contain the given string</param>
        /// <remarks>if all parameters are left blank, the query returns all DataLogItems contained in the database</remarks>
        /// <returns>ArrayList filled with DataLogItems representing the resultset of the query</returns>
        /// <history>created  01.01.2013</history>
        public ArrayList QueryLogItems(
            string strFromTime,
            string strToTime,
            string strLevel,
            string strCategory,
            string strTags,
            string strMessageFilter)
        {
            return _queryLogItems(strFromTime, strToTime, strLevel, strCategory, strTags, strMessageFilter);
        }
        #endregion
        #endregion
    }
}
